package com.yenlien.traceyun.business.dao;

import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.business.entity.BaseSoil;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * @author: bigwangy@sina.com
 * @create: 2020-11-24
 * @description:
 **/
public interface BaseSoilDao extends BaseJpaDao<BaseSoil> {

    @Transactional
    @Modifying
    @Query("update BaseSoil bs set bs.isDelete = true where id = ?1")
    public void ljDelete(String id);


    @Query(value = "SELECT name FROM t_base_soil WHERE id = (SELECT massif_id FROM t_base_soil_equipment WHERE equipment_id = ?1)", nativeQuery = true)
    String getNameByEquId(String equId);

    @Query(value = "SELECT name FROM t_base_soil WHERE id = (SELECT massif_id FROM t_base_soil_equipment WHERE equipment_id = (SELECT id FROM t_equipment WHERE serial = ?1))", nativeQuery = true)
    String getNameByDevId(String devId);


    @Query(value = "SELECT * FROM t_base_soil WHERE type = 2 AND company_id IN (" +
            "SELECT tc.id FROM t_company tc JOIN t_company_customapp_version tcv ON tc.id = tcv.company_id WHERE tc.parent_id = '2c9d0ded821a5f77018224e6b5920001' AND tcv.app_id = ?1)", nativeQuery = true)
    List<BaseSoil> getByAppId(String appId);


    @Query(value = "SELECT tc.credit_code, tbs.`name`, tbs.area, tbs.coordinate address, tbs.type FROM t_base_soil tbs LEFT JOIN t_company tc ON tbs.company_id = tc.id " +
            "WHERE tc.parent_id IN (?1) AND tc.dock_province = '1' AND tbs.type = '1'", nativeQuery = true)
    List<JSONObject> dock3(List<String> parentIdList);

    @Query(value = "SELECT id, tbs.`name`, tbs.area, tbs.coordinate address, tbs.type FROM t_base_soil tbs WHERE tbs.type = 1 AND tbs.company_id = ?1", nativeQuery = true)
    List<JSONObject> dock3(String companyId);


    @Query(value = "SELECT id, serial, rtsp_hd FROM t_equipment WHERE id IN (SELECT equipment_id FROM t_base_soil_equipment WHERE base_soil_id IN (SELECT id FROM t_base_soil WHERE type = ?1 AND pid = ?2))", nativeQuery = true)
    List<JSONObject> dockEqu4(String type, String baseSoilId);


    @Query(value = "SELECT id, `name`, area, principal_name principalName, soil_img soilImg FROM t_base_soil tbs WHERE tbs.type = '2' AND tbs.company_id = ?1 AND is_delete = '0'", nativeQuery = true)
    List<JSONObject> getByCompId(String companyId);

    @Query(value = "SELECT id, `name`, area  FROM t_base_soil tbs WHERE tbs.id = ?1 AND is_delete = '0'", nativeQuery = true)
    List<JSONObject> getByPlotId(String plotId);


    @Query(value = "SELECT id, coordinate, `name`, color, diaphaneity, soil_img soilImg, pid, jy_img jyImg  FROM t_base_soil tbs WHERE tbs.type = '1' AND tbs.company_id = ?1 AND is_delete = '0'", nativeQuery = true)
    List<JSONObject> getByCompanyId(String companyId);

    /**
     * 根据公司id加载所有基地
     *
     * @param companyId
     * @param appId
     * @return
     */
    @Query(value = "select bs from BaseSoil bs where bs.companyId=?1 and bs.type=1 and bs.isDelete=false and bs.appId=?2")
    public List<BaseSoil> loadByCompanyId(String companyId, String appId);


    /**
     * 根据公司id加载所有
     *
     * @param companyId
     * @param appId
     * @return
     */
    @Query(value = "select bs from BaseSoil bs where bs.companyId=?1  and bs.isDelete=false and bs.appId=?2")
    public List<BaseSoil> loadAllByCompanyId(String companyId, String appId);

    /**
     * 根据基地加载下面的类型
     *
     * @param parentId
     * @param type
     * @return
     */
    public List<BaseSoil> findByPidAndType(String parentId, int type);

    /**
     * 根据id查找
     *
     * @param ids
     * @return
     */
    public List<BaseSoil> findByIdIn(Collection<String> ids);


    BaseSoil getById(String id);

    public List<BaseSoil> findByPidIn(Collection<String> pids);

    public List<BaseSoil> findByCompanyId(String companyId);


    @Query(value = "SELECT * FROM t_base_soil WHERE company_id = ?1 AND name LIKE CONCAT('%', ?2, '%')", nativeQuery = true)
    List<BaseSoil> findByCompIdName(String companyId, String name);


    public List<BaseSoil> findByCompanyIdAndAppId(String companyId, String appId);

    //地块数量
    @Query(value = "select count(id) from t_base_soil where company_id=?1 and type=2 and app_id=002 and is_delete=false", nativeQuery = true)
    int areaNumCount(String companyId);

    @Query(value = "select count(id) from t_base_soil where company_id=?1 and type=2 and is_delete=false", nativeQuery = true)
    int breedBaseCount(String companyId);


    @Query(value = "select id,company_id,createtime,last_step,user_id,app_id,area,color,coordinate,diaphaneity,img_time,is_delete,jy_img,name,peasant_id,peasant_name," +
            "pid,principal_id,principal_name,soil_img,type,cross_point,recall from t_base_soil where company_id=?1 and type=3 AND is_delete = false", nativeQuery = true)
    List<BaseSoil> finByBasesoilId(String companyId);


    @Query(value = "select count(id) from t_base_soil where type =2 and company_id=?1 and is_delete=false", nativeQuery = true)
    int areaNumSum(String companyId);

    //地块基本信息查询
//    @Query(value="select bs.id,bs.company_id,bs.name,bs.principal_name,bs.coordinate," +
//            "u.username,u.phone,bs.soil_img,(select sum(area) from t_base_soil bs " +
//            " inner join t_user u on u.id=bs.user_id where bs.type=2 and bs.is_delete=false) as bsAreaSum" +
//            ",(select sum(plant_area) from t_plant p " +
//            " inner join t_user u on u.id=p.user_id where p.company_id=?1) as plantAreaSum" +
//            " from t_base_soil bs" +
//            " inner join t_user u on u.id=bs.user_id" +
//            " where bs.company_id=?1 and bs.type=2" +
//            " group by bs.user_id",nativeQuery = true)

    @Query(value = "SELECT bs.id,bs.company_id,bs. NAME,bs.principal_name,bs.coordinate," +
            "u.username,u.phone,bs.soil_img,bs.area as bsAreaSum FROM t_base_soil bs" +
            " INNER JOIN t_user u ON u.id = bs.user_id" +
            " WHERE bs.company_id =?1 AND bs.type = 2", nativeQuery = true)
    List<Map<String, Object>> userAndBaseSoil(String companyId);

    @Query(value = "select sum(plant_area) as plantArea from t_plant p " +
            " inner join t_base_soil bs on bs.id=p.soil_id where p.company_id =?1 and p.soil_id=?2", nativeQuery = true)
    List<Map<String, Object>> sumPlantArae(String companyId, String soilId);


    /**
     * 根据公司ID获取该公司已新建的基地数
     *
     * @param companyId
     * @return
     */
    @Query(value = "SELECT count(*) FROM t_base_soil WHERE company_id = ?1 AND type = ?2", nativeQuery = true)
    int getNum(String companyId, int type);

    @Query(value ="SELECT * FROM t_base_soil WHERE type = 2 AND company_id IN (" +
            "SELECT tc.id FROM t_company tc JOIN t_company_customapp_version tcv ON tc.id = tcv.company_id WHERE tc.parent_id = '2c9d0ded821a5f77018224e6b5920001')",nativeQuery = true)
    List<BaseSoil> findByBaseSoil();
}
